package study.itwill.team5.house;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.itwill.common.DBHandler;
import study.itwill.common.DateTime;


public class House {
	
	
	// Logger 클래스의 인스턴스를 받아온다.
	static Logger logger = Logger.getLogger(House.class);
		
	private static final String TABLE = "house";
	
	String datetime = DateTime.getInstance().getNowDateTimeString();

	/**
	 * 민박 추가 기능(관리자)
	 * 
	 * @param name			- 민박 상호명
	 * @param address1		- 전화번호 ( 국가 )
	 * @param address2		- 전화번호 ( 앞자리 )
	 * @param address3		- 전화번호 ( 가운데 )
	 * @param address4		- 전화번호 ( 끝자리 )
	 * @param email			- 민박 이메일
	 * @param homepage		- 민박 홈페이지
	 * @param info			- 민박 상세정보
	 * @param lat			- 위도
	 * @param lon			- 경도
	 * @param tel			- 연락처 배열
	 * @param imgae			- 이미지 배열
	 * @param room			- 룸정보 객체배열
	 * @return				- 추가된 회원의 일련번호
	 */
	public int addHouse(String name, String address1, String address2, 
						String address3, String address4, String email, 
						String homepage, String info, double lat, double lon, int house_city_seq, 
						String[] tel, String[] image, ArrayList<RoomItem> room) {
		logger.debug("=============== method start >> addHouse");
		logger.debug(" ------ [PARAMS INFO] ------");
		logger.debug("[PARAMS] name=" + name);
		logger.debug("[PARAMS] address1=" + address1);
		logger.debug("[PARAMS] address2=" + address2);
		logger.debug("[PARAMS] address3=" + address3);
		logger.debug("[PARAMS] address4=" + address4);
		logger.debug("[PARAMS] email=" + email);
		logger.debug("[PARAMS] homepage=" + homepage);
		logger.debug("[PARAMS] info=" + info);
		logger.debug("[PARAMS] lat=" + lat);
		logger.debug("[PARAMS] lon=" + lon);
		logger.debug("[PARAMS] house_city_seq=" + house_city_seq);
		for(int i=0; i<tel.length; i++) {
			logger.debug("[PARAMS] tel=" + tel[i]);
		}
		for(int i=0; i<image.length; i++) {
			logger.debug("[PARAMS] image=" + image[i]);
		}
		logger.debug("[PARAMS] room=" + room);
		
		int seq = -1;
		
		// SQL 인젝션 방어
		name = name.replace("'", "\'");
		address1 = address1.replace("'", "\'");
		address2 = address2.replace("'", "\'");
		address3 = address3.replace("'", "\'");
		address4 = address4.replace("'", "\'");
		email = email.replace("'", "\'");
		homepage = homepage.replace("'", "\'");
		info = info.replace("'", "\'");
		for (int i=0; i<tel.length; i++) {
			tel[i] = tel[i].replace("'", "\'");
		}
		for (int i=0; i<image.length; i++) {
			image[i] = image[i].replace("'", "\'");
		}
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			String temp = item.getName();
			temp = temp.replace("'", "\'");
		}
		// 트랜젝션에 대한 판단이 잘 서지 않는다면, 무조건 하면 된다.
		DBHandler.beginTrans();
		
		// INSERT 처리를 위한 구현
		String sql1 = "INSERT INTO house("
	            + "name, address1, address2, address3, address4, email, homepage, info, lat, lon, reg_date, edit_date, house_city_seq"
	            + ") VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %04f, %04f, '%s', '%s', %d)";
		String sql2 = "INSERT INTO house_tel("
				+ "tel, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', '%s', '%s', %d)";
		String sql3 = "INSERT INTO house_image("
				+ "image_url, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', '%s', '%s', %d)";
		String sql4 = "INSERT INTO house_room("
				+ "name, limit_person, off_price, on_price, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', %d, %d, %d, '%s', '%s', %d)";
		
		boolean result = DBHandler.execSql(sql1, name, address1, address2, address3, address4, email, 
				homepage, info, lat, lon, datetime, datetime, house_city_seq);
		
		// 데이터 조회
		String sql = "SELECT seq "
				+ "FROM house "
				+ "WHERE name = '%s'";
		
		ResultSet rs = DBHandler.getResult(sql, name);
		int num = -1;
		if(rs != null) {
			try {
				rs.next();
				num = rs.getInt(1);
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		for (int i=0; i<tel.length; i++) {
			result = DBHandler.execSql(sql2, tel[i], datetime, datetime, num);
		}
		for (int i=0; i<image.length; i++) {
			result = DBHandler.execSql(sql3, image[i], datetime, datetime, num);
		}
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			result = DBHandler.execSql(sql4, item.getName(), item.getLimit_person(), item.getOff_price(), item.getOn_price(), datetime, datetime, num);
		}
		
		//-----------------------------------------
		// 결과처리
		//-----------------------------------------
		if(result) {
			DBHandler.commit();
			seq =  DBHandler.getMax(TABLE, "seq", null);
			logger.debug("처리결과 >> " + seq + "번 민박 추가 성공");
		} else {
			DBHandler.rollback();
			logger.error("처리결과 >> 민박 추가 실패");
		}
		
		logger.debug("=============== method end >> addHouse");
		
		
		return seq;
	}
	
	/**
	 * 민박 목록 기능
	 * @param house_seq		- 민박 테이블 일련번호
	 * @return				- HouseItem 
	 */
	public HouseItem viewHouse(int house_seq) {
		
		logger.debug("=============== method start >> viewHouse");
		logger.debug(" ------ [PARAMS INFO] ------");
		logger.debug("[PARAMS] house_seq=" + house_seq);
		
		HouseItem item = null;
		
		// 데이터 조회
		String sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.reg_date, house.edit_date, house_city_seq, lat, lon "
				+ "FROM house, house_city "
				+ "WHERE house.house_city_seq = house_city.seq "
				+ "AND house.seq = %d";
		
		ResultSet rs = DBHandler.getResult(sql, house_seq);
		
		if(rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				rs.next();
				
				// 1) ResultSet의 모든 데이터를 변수로 추출
				int seq = rs.getInt(1);
				String city_name = rs.getString(2);
				String name = rs.getString(3);
				String address1 = rs.getString(4);
				String address2 = rs.getString(5);
				String address3 = rs.getString(6);
				String address4 = rs.getString(7);
				String email = rs.getString(8);
				String homepage = rs.getString(9);
				String info = rs.getString(10);
				double grade = rs.getDouble(11);
				String reg_date = rs.getString(12);
				String edit_date = rs.getString(13);
				int house_city_seq = rs.getInt(14);
				double lat = rs.getDouble(15);
				double lon = rs.getDouble(16);
				
				// 2) 추출된 값을 객체로 구성
				item = new HouseItem(seq, city_name, name, address1, address2, address3, address4, email, homepage, info, lat, lon, grade, reg_date, edit_date, house_city_seq);
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 3) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(item == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공");
			logger.debug(item.toString());
		}
				
		logger.debug("=============== method end >> viewHouse");
		
		return item;
		
	}

	/**
	 * 민박 목록 기능
	 * @param choice		- 검색 키워드 = 0: 베네치아, 1: 로마
	 * @return				- HouseItem (ArrayList)
	 */
	public ArrayList<HouseItem> listHouse(int choice, String search, int start, int end) {
		
		logger.debug("========== method start >> listHouse");
		logger.debug(" ------ [PARAMS INFO] ------");
		logger.debug("[PARAMS] choice=" + choice);
		
		ArrayList<HouseItem> list = null;
		
		// 데이터 조회
		String sql = null;
		
		/** 민박 검색을 위한 파라미터 정의 
		 * 	0, null : 민박 전체 검색
		 *  1, null : 베네치아 도시 전체 검색
		 *  2, null : 로마 도시 전체 검색
		 *  3, 민박 이름 검색
		 *  4, 국가 검색
		 *  5, 도시 검색
		 *  6, 상세 번지 검색
		 *  7, 약식주소 검색
		 *  8, 이메일 검색
		 *  9, 홈페이지 검색
		 */
		
		switch (choice) {
		case 0:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.reg_date, house.edit_date, house_city_seq, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "ORDER BY desc";
			break;
		case 1:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.reg_date, house.edit_date, house_city_seq, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND house_city.seq = " + choice + " ORDER BY house.seq desc LIMIT %d, %d";
					
			break;
		case 2:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.reg_date, house.edit_date, house_city_seq, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND house_city.seq = " + choice + " ORDER BY house.seq desc LIMIT %d, %d";
			break;
		case 3:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND name like '%%" + search + "%%'";
			break;
		case 4:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND address1 like '%%" + search + "%%'";
			break;
		case 5:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND address2 like '%%" + search + "%%'";
			break;
		case 6:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND address3 like '%%" + search + "%%'";
			break;
		case 7:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND address4 like '%%" + search + "%%'";
			break;
		case 8:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND email like '%%" + search + "%%'";
			break;
		case 9:
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND homepage like '%%" + search + "%%'";
			break;
		default:
			System.out.println("검색조건 선택이 올바르지 않습니다.");
			sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, house.house_city_seq, house.reg_date, house.edit_date, lat, lon "
					+ "FROM house, house_city "
					+ "WHERE house.house_city_seq = house_city.seq "
					+ "AND seq=-1";
			break;
		}

		
		ResultSet rs = DBHandler.getResult(sql, start, end);
		
		if(rs != null) {
			try {
				
				list = new ArrayList<HouseItem>();

				while (rs.next()) {
					// 1) ResultSet의 모든 데이터를 변수로 추출
					int seq = rs.getInt(1);
					String city_name = rs.getString(2);
					String name = rs.getString(3);
					String address1 = rs.getString(4);
					String address2 = rs.getString(5);
					String address3 = rs.getString(6);
					String address4 = rs.getString(7);
					String email = rs.getString(8);
					String homepage = rs.getString(9);
					String info = rs.getString(10);
					double grade = rs.getDouble(11);
					String reg_date = rs.getString(12);
					String edit_date = rs.getString(13);
					int house_city_seq = rs.getInt(14);
					double lat = rs.getDouble(15);
					double lon = rs.getDouble(16);
					
					
					// 2) 추출된 값을 객체로 구성
					HouseItem item = new HouseItem(seq, city_name, name, address1, address2, address3, address4, email, homepage, info, lat, lon, grade, reg_date, edit_date, house_city_seq);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try{
					rs.close();
				} catch (SQLException e){
					rs = null;
				}
				
			}
		}
		
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		
		logger.debug("========== method end >> listHouse");
		
		return list;
		
	}

	/**
	 * 민박 수정 기능(관리자)
	 * @param name			- 민박 상호명
	 * @param address1		- 전화번호 ( 국가 )
	 * @param address2		- 전화번호 ( 앞자리 )
	 * @param address3		- 전화번호 ( 가운데 )
	 * @param address4		- 전화번호 ( 끝자리 )
	 * @param email			- 민박 이메일
	 * @param homepage		- 민박 홈페이지
	 * @param info			- 민박 상세정보
	 * @return				- 수정 성공 - true, 수정 실패 - false
	 */
	public boolean editHouse(int seq, String name, String address1, String address2, 
						String address3, String address4, String email, 
						String homepage, String info, int house_city_seq, 
						String[] tel, String[] image, ArrayList<RoomItem> room) {
		logger.debug("========== method start >> editHouse");
		logger.debug(" ------ [PARAMS INFO] ------");
		logger.debug("[PARAMS] seq=" + seq);
		logger.debug("[PARAMS] name=" + name);
		logger.debug("[PARAMS] address1=" + address1);
		logger.debug("[PARAMS] address2=" + address2);
		logger.debug("[PARAMS] address3=" + address3);
		logger.debug("[PARAMS] address4=" + address4);
		logger.debug("[PARAMS] email=" + email);
		logger.debug("[PARAMS] homepage=" + homepage);
		logger.debug("[PARAMS] info=" + info);
		logger.debug("[PARAMS] house_city_seq=" + house_city_seq);
		for(int i=0; i<tel.length; i++) {
			logger.debug("[PARAMS] tel=" + tel[i]);
		}
		for(int i=0; i<image.length; i++) {
			logger.debug("[PARAMS] image=" + image[i]);
		}
		logger.debug("[PARAMS] room=" + room);
		
		// SQL 인젝션 방어
		for (int i=0; i<tel.length; i++) {
			tel[i] = tel[i].replace("'", "\'");
		}
		for (int i=0; i<image.length; i++) {
			image[i] = image[i].replace("'", "\'");
		}
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			String temp = item.getName();
			temp = temp.replace("'", "\'");
		}
		
		DBHandler.beginTrans();
		// 민박 수정
		String sql1 = "UPDATE house SET "
				+ "name = '%s', address1 = '%s', address2 = '%s', address3 = '%s', address4 = '%s', "
				+ "email = '%s', homepage = '%s', info = '%s', edit_date = '%s', house_city_seq = %d "
				+ "WHERE seq = %d";
		// 연락처 삭제 후 생성
		String sql2 = "DELETE FROM house_tel WHERE house_seq = %d";
		String sql3 = "INSERT INTO house_tel("
				+ "tel, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', '%s', '%s', %d)";
		// 이미지 삭제 후 생성
		String sql4 = "DELETE FROM house_image WHERE house_seq = %d";
		String sql5 = "INSERT INTO house_image("
				+ "image_url, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', '%s', '%s', %d)";
		// 룸 삭제 후 생성
		String sql6 = "DELETE FROM house_room WHERE house_seq = %d";
		String sql7 = "INSERT INTO house_room("
				+ "name, limit_person, off_price, on_price, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', %d, %d, %d, '%s', '%s', %d)";	
		
		// 민박 정보 수정 쿼리
		boolean result = DBHandler.execSql(sql1, name, address1, address2, address3, address4, email, 
				homepage, info, datetime, house_city_seq, seq);
		
		// 데이터 조회
		String sql = "SELECT seq "
				+ "FROM house "
				+ "WHERE name = '%s'";
		
		ResultSet rs = DBHandler.getResult(sql, name);
		int num = -1;
		if(rs != null) {
			try {
				rs.next();
				num = rs.getInt(1);
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		// 연락처 정보 수정 쿼리
		result = DBHandler.execSql(sql2, num);
		for (int i=0; i<tel.length; i++) {
			result = DBHandler.execSql(sql3, tel[i], datetime, datetime, num);
		}
		
		// 이미지 정보 수정 쿼리
		result = DBHandler.execSql(sql4, num);
		for (int i=0; i<image.length; i++) {
			result = DBHandler.execSql(sql5, image[i], datetime, datetime, num);
		}
		
		// 룸 정보 수정 쿼리
		result = DBHandler.execSql(sql6, num);
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			result = DBHandler.execSql(sql7, item.getName(), item.getLimit_person(), item.getOff_price(), item.getOn_price(), datetime, datetime, num);
		}
		
		if(result) {
			DBHandler.commit();
			logger.debug("처리결과 >> " + seq + "번 민박 수정 성공");
		} else {
			DBHandler.rollback();
			logger.debug("처리결과 >> 데이터 수정 실패");
		}
		
		logger.debug("========== method end >> editHouse");
		
		return result;
		
	}

	/**
	 * 민박 삭제 기능 (관리자)
	 * @param house_seq		- 민박 일련 번호
	 * @return				- 삭제 성공 - true, 삭제 실패 - false
	 */
	public boolean deleteHouse(int house_seq) {
		logger.debug("========== method start >> deleteHouse");
		logger.debug(" ------ [PARAMS INFO] ------");
		logger.debug("[PARAMS] house_seq=" + house_seq);
		
		boolean result  = false;
		
		DBHandler.beginTrans();
		
		String sql0 = "DELETE FROM house_tel WHERE house_seq=%d";
		String sql1 = "DELETE FROM house_room WHERE house_seq=%d";
		String sql2 = "DELETE FROM house_image WHERE house_seq=%d";
		String sql3	= "DELETE FROM wishlist WHERE house_seq=%d";
		String sql4 = "DELETE FROM house WHERE seq=%d";
		
		result = DBHandler.execSql(sql0, house_seq);
		result = DBHandler.execSql(sql1, house_seq);
		result = DBHandler.execSql(sql2, house_seq);
		result = DBHandler.execSql(sql3, house_seq);
		result = DBHandler.execSql(sql4, house_seq);
		
		if(result) {
			DBHandler.commit();
			logger.debug("처리결과 >> " + house_seq + "번 민박 삭제 성공");
		} else {
			DBHandler.rollback();
			logger.debug("처리결과 >> 데이터 삭제 실패");
		}
		logger.debug("========== method end >> deleteHouse");
		
		return result;
	}

	/**
	 * 민박 검색 기능 (모든 민박 검색 용)
	 * @param choice		- 검색 키워드 = 0 ~ 7
	 * @return				- searchNameHouse 메서드에 search값 null로 리턴
	 */
	public ArrayList<HouseItem> listHouse(int choice, int start, int end) {
		return listHouse(choice, null, start, end);
	}
	
	/**
	 * 주변의 민박 검색 후 리스트로 리턴한다.
	 * @return	ArrayList		- 검색 결과 리턴
	 */
	public ArrayList<HouseItem> surround() {
		ArrayList<HouseItem> item = null;
		
		return item;
	}
	
	/**
	 * 민박 일련번호를 참조하여, 해당 민박의 전화번호를 리턴한다.
	 * @param house_seq			- 민박 일련번호
	 * @return	ArrayList		- 검색 결과 리턴
	 */
	public ArrayList<TelItem> telList(int house_seq) {
		logger.debug("========== method start >> telList");
		ArrayList<TelItem> list = null;
		
		String sql = "SELECT t.seq, h.name, t.tel, t.reg_date, t.edit_date, t.house_seq "
				+ "FROM house_tel t "
				+ "INNER JOIN house h "
				+ "ON t.house_seq = h.seq "
				+ "WHERE t.house_seq = %d";
		
		ResultSet rs = DBHandler.getResult(sql, house_seq);
		
		if(rs != null) {
			try {
				
				list = new ArrayList<TelItem>();
				
				while (rs.next()) {
					// 1) ResultSet의 모든 데이터를 변수로 추출
					int seq = rs.getInt(1);
					String house_name = rs.getString(2);
					String tel = rs.getString(3);
					String reg_date = rs.getString(4);
					String edit_date = rs.getString(5);
					int n_house_seq = rs.getInt(6);
					
					// 2) 추출된 값을 객체로 구성
					TelItem item = new TelItem(seq, house_name, tel, reg_date, edit_date, n_house_seq);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try{
					rs.close();
				} catch (SQLException e){
					rs = null;
				}
				
			}
		}
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		logger.debug("========== method end >> telList");
		return list;
	}
	
	/**
	 * 민박 일련번호를 참고하여, 해당 민박의 이미지를 리턴한다.
	 * @param house_seq			- 민박 일련번호
	 * @return	ArrayList		- 검색 결과 리턴
	 */
	public ArrayList<ImageItem> imageList(int house_seq) {
		logger.debug("========== method start >> imageList");
		ArrayList<ImageItem> list = null;
		
		String sql = "SELECT i.seq, h.name, i.image_url, i.reg_date, i.edit_date, i.house_seq "
				+ "FROM house_image i "
				+ "INNER JOIN house h "
				+ "ON i.house_seq = h.seq "
				+ "WHERE i.house_seq = %d";
		
		ResultSet rs = DBHandler.getResult(sql, house_seq);
		
		if(rs != null) {
			try {
				
				list = new ArrayList<ImageItem>();
				
				while (rs.next()) {
					// 1) ResultSet의 모든 데이터를 변수로 추출
					int seq = rs.getInt(1);
					String house_name = rs.getString(2);
					String image_url = rs.getString(3);
					String reg_date = rs.getString(4);
					String edit_date = rs.getString(5);
					int n_house_seq = rs.getInt(6);
					
					// 2) 추출된 값을 객체로 구성
					ImageItem item = new ImageItem(seq, house_name, image_url, reg_date, edit_date, n_house_seq);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try{
					rs.close();
				} catch (SQLException e){
					rs = null;
				}
				
			}
		}
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		logger.debug("========== method end >> imageList");
		return list;
	}
	
	public ArrayList<RoomItem> roomList(int house_seq) {
		logger.debug("========== method start >> roomList");
		ArrayList<RoomItem> list = null;
		
		String sql = "SELECT r.name, r.limit_person, r.off_price, r.on_price, r.reg_date, r.edit_date, r.house_seq "
				+ "FROM house_room r "
				+ "INNER JOIN house h "
				+ "ON r.house_seq = h.seq "
				+ "WHERE r.house_seq = %d";
		
		ResultSet rs = DBHandler.getResult(sql, house_seq);
		
		if(rs != null) {
			try {
				
				list = new ArrayList<RoomItem>();
				
				while (rs.next()) {
					// 1) ResultSet의 모든 데이터를 변수로 추출
					String name = rs.getString(1);
					int limit_person = rs.getInt(2);
					int off_price = rs.getInt(3);
					int on_price = rs.getInt(4);
					
					// 2) 추출된 값을 객체로 구성
					RoomItem item = new RoomItem(name, limit_person, off_price, on_price);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try{
					rs.close();
				} catch (SQLException e){
					rs = null;
				}
				
			}
		}
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		logger.debug("========== method end >> roomList");
		return list;
	}
}
